package utils;// Copyright (C), 2012-2018, Sichuan Xiaoka Technology Co., Ltd.


import analysis.ExcelValueHandle;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLDecoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * excel集成工具类
 * 本方法基于poi jar包
 *
 * @author 张鹏
 */
public class ExcelUtils {

    private final static String EXCEL2003 = "xls";

    private final static String EXCEL2007 = "xlsx";


    /**
     * 导出excel工具
     *
     * @param data 注解的数据
     */
    public static byte[] exportExcel(List data) {
        if (data == null || data.size() <= 0) {
            return null;
        }
        Object o = data.get(0);
        //获取处理数据
        Map<String, String> stringStringMap = ExcelValueHandle.annotationExcel(o.getClass());
        if (stringStringMap.size() <= 0) {
            return null;
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            Workbook workbook = isExcel(EXCEL2007);
            if (workbook == null) {
                return null;
            }
            //创建一个sheet
            Sheet sheet = workbook.createSheet();
            //创建第一行
            Row row = sheet.createRow(0);
            //填充头数据
            int count = 0;
            for (String header : stringStringMap.keySet()) {
                row.createCell(count).setCellValue(header);
                count++;
            }
            //填充数据体
            for (int i = 0; i < data.size(); i++) {
                count = 0;
                //创建行
                Row row1 = sheet.createRow(i + 1);
                //通过反射获取字段数据装填表格
                Object ob = data.get(i);
                Class<?> aClass = ob.getClass();
                //读数据
                for (String header : stringStringMap.keySet()) {
                    String name = stringStringMap.get(header);
                    String methodStr = "get" + name.toUpperCase().substring(0, 1) + name.substring(1);
                    Method method = aClass.getMethod(methodStr);
                    String value = String.valueOf(method.invoke(ob));
                    row1.createCell(count).setCellValue(value);
                    count++;
                }
            }
            workbook.write(outputStream);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return outputStream.toByteArray();
    }

    /**
     * 导出excel工具
     *
     * @param header 表格头
     * @param data   表格数据
     */
    public static byte[] exportExcel(String header, List data) {
        try {
            header = URLDecoder.decode(header, "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        String[] split = header.split(",");
        if (data == null || data.size() <= 0) {
            return null;
        }
        Object o = data.get(0);
        //获取处理数据
        Map<String, String> stringStringMap = ExcelValueHandle.annotationExcel(o.getClass());
        if (stringStringMap.size() <= 0) {
            return null;
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            Workbook workbook = isExcel(EXCEL2003);
            if (workbook == null) {
                return null;
            }
            //创建一个sheet
            Sheet sheet = workbook.createSheet();
            //创建第一行
            Row row = sheet.createRow(0);
            //填充头数据
            int count = 0;
            for (String top : split) {
                row.createCell(count).setCellValue(top);
                count++;
            }
            //填充数据体
            for (int i = 0; i < data.size(); i++) {
                count = 0;
                //创建行
                Row row1 = sheet.createRow(i + 1);
                //通过反射获取字段数据装填表格
                Object ob = data.get(i);
                Class<?> aClass = ob.getClass();
                //读数据
                for (String top : split) {
                    String name = stringStringMap.get(top);
                    if (StringUtils.isBlank(name)) {
                        row1.createCell(count).setCellValue("");
                        count++;
                        continue;
                    }
                    String methodStr = "get" + name.toUpperCase().substring(0, 1) + name.substring(1);
                    Method method = aClass.getMethod(methodStr);
                    String value = String.valueOf(method.invoke(ob));
                    row1.createCell(count).setCellValue(value);
                    count++;
                }
            }
            workbook.write(outputStream);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return outputStream.toByteArray();
    }


    /**
     * 导出excel工具
     *
     * @param header 表格头
     * @param data   表格数据
     */
    public static byte[] exportExcelToArray(String header, List data) {
        try {
            header = URLDecoder.decode(header, "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        String[] split = header.split(",");
        if (data == null || data.size() <= 0) {
            return null;
        }
        Object o = data.get(0);
        //获取处理数据
        Map<String, String[]> stringStringMap = ExcelValueHandle.annotationExcelToArray(o.getClass());
        if (stringStringMap.size() <= 0) {
            return null;
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            Workbook workbook = isExcel(EXCEL2007);
            if (workbook == null) {
                return null;
            }
            //创建一个sheet
            Sheet sheet = workbook.createSheet();
            //创建第一行
            Row row = sheet.createRow(0);
            //填充头数据
            int count = 0;
            for (String top : split) {
                row.createCell(count).setCellValue(top);
                count++;
            }
            //填充数据体
            for (int i = 0; i < data.size(); i++) {
                count = 0;
                //创建行
                Row row1 = sheet.createRow(i + 1);
                //通过反射获取字段数据装填表格
                Object ob = data.get(i);
                Class<?> aClass = ob.getClass();
                //读数据
                for (String top : split) {
                    String[] name = stringStringMap.get(top);
                    if (name == null) {
                        row1.createCell(count).setCellValue("");
                        count++;
                        continue;
                    }
                    String methodStr = "get" + name[0].toUpperCase().substring(0, 1) + name[0].substring(1);
                    Method method = aClass.getMethod(methodStr);
                    String value = "";
                    Object invoke = method.invoke(ob);
                    if (invoke != null) {
                        value = String.valueOf(invoke);
                        //时间
                        if (StringUtils.isNotBlank(name[1])) {
                            Long created = (Long) invoke;
                            SimpleDateFormat formatter = new SimpleDateFormat(name[1]);
                            value = formatter.format(created * 1000);
                        }
                        //类型
                        if (StringUtils.isNotBlank(name[2])) {
                            int i1 = Integer.parseInt(String.valueOf(method.invoke(ob)));
                            String[] split1 = name[2].split(",");
                            for (int j = 0; j < split1.length; j++) {
                                String[] split2 = split1[j].split(":");
                                if (Integer.parseInt(split2[0]) == i1) {
                                    value = split2[1];
                                }
                            }
                        }
                    }

                    row1.createCell(count).setCellValue(value);
                    count++;
                }
            }
            workbook.write(outputStream);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return outputStream.toByteArray();
    }

    /**
     * 导出excel工具
     *
     * @param header 表格头
     * @param data   表格数据
     */
    public static byte[] exportExcel(String[] header, List data) {
        return exportExcel(EXCEL2007, header, data);
    }

    /**
     * 导出excel工具
     *
     * @param format 导出格式xls,xlsx
     * @param header 表格头
     * @param data   表格数据
     */
    public static byte[] exportExcel(String format, String[] header, List data) {
        //数据校验
        if (StringUtils.isBlank(format)) {
            format = EXCEL2007;
        }
        if (header == null || header.length < 0) {
            return null;
        }
        if (data == null || data.size() <= 0) {
            return null;
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            Workbook workbook = isExcel(format);
            if (workbook == null) {
                return null;
            }
            //创建一个sheet
            Sheet sheet = workbook.createSheet();
            //创建第一行
            Row row = sheet.createRow(0);
            //填充头数据
            for (int i = 0; i < header.length; i++) {
                row.createCell(i).setCellValue(header[i]);
            }

            //填充数据
            Object o = data.get(0);
            if (o instanceof Map) {
                for (int i = 0; i < data.size(); i++) {
                    //创建行
                    Row row1 = sheet.createRow(i + 1);
                    //通过反射获取字段数据装填表格
                    Object ob = data.get(i);
                    Map map = (Map) ob;
                    int num = 0;
                    for (Object key : map.keySet()) {
                        String value = String.valueOf(map.get(key));
                        row1.createCell(num).setCellValue(value);
                        num++;
                    }
                }
            } else if (o instanceof Object) {
                for (int i = 0; i < data.size(); i++) {
                    //创建行
                    Row row1 = sheet.createRow(i + 1);
                    //通过反射获取字段数据装填表格
                    Object ob = data.get(i);
                    Class<?> aClass = ob.getClass();
                    Field[] fields = aClass.getDeclaredFields();
                    //读数据
                    for (int j = 0; j < fields.length; j++) {
                        Field f = fields[j];
                        PropertyDescriptor pd = new PropertyDescriptor(f.getName(), aClass);
                        //获得读方法
                        Method rM = pd.getReadMethod();
                        //封装数据
                        String value = String.valueOf(rM.invoke(ob));
                        row1.createCell(j).setCellValue(value);
                    }
                }
            } else {
                return null;
            }

            workbook.write(outputStream);
        } catch (IntrospectionException ie) {
            ie.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return outputStream.toByteArray();
    }

    /**
     * 判断是否属于excel
     *
     * @param format
     * @return
     */
    private static Workbook isExcel(String format) {
        if (StringUtils.isBlank(format)) {
            return null;
        }
        Workbook workbook = null;
        //判定文档格式
        if (EXCEL2003.equals(format) || format.endsWith(EXCEL2003)) {
            workbook = new HSSFWorkbook();
        } else if (EXCEL2007.equals(format) || format.endsWith(EXCEL2007)) {
            workbook = new XSSFWorkbook();
        }
        return workbook;
    }

    /**
     * 判断是否属于excel
     *
     * @param format
     * @return
     */
    private static Workbook isExcel(String format, byte[] file) throws IOException {
        if (StringUtils.isBlank(format)) {
            return null;
        }

        InputStream inputStream = new ByteArrayInputStream(file);

        Workbook workbook = null;
        //判定文档格式
        if (EXCEL2003.equals(format) || format.endsWith(EXCEL2003)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (EXCEL2007.equals(format) || format.endsWith(EXCEL2007)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }


    /**
     * 导入excel工具
     *
     * @param file  MultipartFile
     * @param clasz 获取封装的对象的class
     * @return
     */
    public static List<Object> importExcel(String fileName, byte[] file, Class clasz) {
        List<Object> objects = null;
        try {
            objects = excelImportHandle(fileName, file, clasz);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objects;
    }
//
//    /**
//     * 导入excel工具
//     *
//     * @param request HttpServletRequest
//     * @param name    获取上传名称
//     * @param clasz   获取封装的对象的class
//     * @return
//     */
//    public static List<Object> importExcel(HttpServletRequest request, String name, Class clasz) {
//        List<Object> objects = null;
//        try {
//            //文件存储
//            MultipartHttpServletRequest mreq = (MultipartHttpServletRequest) request;
//            MultipartFile file = mreq.getFile(name);
//            String fileName = file.getOriginalFilename();
//
//            objects = excelImportHandle(fileName, file.getBytes(), clasz);
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        return objects;
//    }

    /**
     * 导入exel封装
     *
     * @param fileName 文件名称
     * @param file     数据
     * @param clasz    获取封装的对象的class
     * @return
     */
    public static List<Object> excelImportHandle(String fileName, byte[] file, Class clasz) {
        List<Object> result = new ArrayList<>();
        try {
            Workbook workbook = isExcel(fileName, file);
            if (workbook == null) {
                return null;
            }
            //获取处理数据
            Map<String, String> stringStringMap = ExcelValueHandle.annotationExcel(clasz);
            if (stringStringMap.size() <= 0) {
                return null;
            }
            //实例化类型
            List<String> header = new ArrayList<>();
            //开始读取excel
            //读取第一个sheet
            Sheet sheetAt = workbook.getSheetAt(0);
            //获取总行数
            for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
                //跳过第一行
                if (i == 0) {
                    Row row = sheetAt.getRow(i);
                    for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                        Cell cell = row.getCell(j);
                        Object o = valueType(j, cell);
                        if (o != null) {
                            header.add(String.valueOf(o));
                        }
                    }
                } else {
                    Object ob = clasz.newInstance();
                    //获取二行
                    Row row = sheetAt.getRow(i);
                    for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                        Cell cell = row.getCell(j);
                        Object o = valueType(j, cell);
                        ExcelValueHandle.setValue(ob, clasz, stringStringMap.get(header.get(j)), o);
                    }
                    result.add(ob);
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();

        }
        return result;
    }


    /**
     * 数据类型转换
     *
     * @param num  索引
     * @param cell 表格数据
     * @return
     */
    private static Object valueType(int num, Cell cell) {
        Object value;
        // 以下是判断数据的类型
        switch (cell.getCellTypeEnum()) {
            // 数字s
            case NUMERIC:
                //如果为时间格式的内容
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    break;
                } else {
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            // 字符串
            case STRING:
                value = cell.getStringCellValue();
                break;
            // Boolean
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            // 公式
            case FORMULA:
                value = cell.getCellFormula();
                break;
            // 空值
            case BLANK:
                value = "";
                break;
            // 故障
            case ERROR:
                value = "";
                break;
            default:
                num = num + 1;
                throw new ClassCastException("导入文件的第[" + num + "]行的格式有问题,请检查!");
        }
        return value;
    }
}

